/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Metodos;

import Atributos.Curso;
import Atributos.Serie;
import BD.CriaConexao;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Andre
 */
public class SerieMet {
    private Connection conexao;
     public SerieMet(){
        try {
            this.conexao=(Connection) CriaConexao.getConexao();
        } catch (SQLException ex) {
            Logger.getLogger(CursoMet.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public boolean VerificaBD(Curso c, Serie s) {
        String sql = "select serie.nome_serie,curso.nome_curso,descricao.descricao_desc from serie,descricao,curso where serie.id_curso_s=curso.id_curso and serie.id_turma_s=descricao.id_desc and descricao.id_curso_desc=curso.id_curso and serie.nome_serie=? and curso.nome_curso=? and descricao.descricao_desc=?";
        int decisao=0;
        try {
            try (PreparedStatement stmt = (PreparedStatement) conexao.prepareStatement(sql)) {
                stmt.setString(1,s.getNome());
                stmt.setString(2,c.getNome());
                stmt.setString(3,c.getDescrição());
                try (ResultSet rs = stmt.executeQuery()) {
                    while (rs.next()) {
                        if(rs.getString("nome_serie").equals(s.getNome()) && rs.getString("nome_curso").equals(c.getNome()) && rs.getString("descricao_desc").equals(c.getDescrição())){
                         decisao=1;   
                        }            
                        
                    }
                }
            }
        } catch (SQLException ex) {
            Logger.getLogger(DisciplinaMet.class.getName()).log(Level.SEVERE, null, ex);
        }
        
            if(decisao==1){
                return false;
            }else{
               return true; 
            }
    }

    public void AdicionaSerie(Serie s) throws SQLException {
         String sql_a = "insert into serie (nome_serie,id_curso_s, id_turma_s) values (?,?,?)";
        try (PreparedStatement stmt = (PreparedStatement) conexao.prepareStatement(sql_a)) {
            stmt.setString(1, s.getNome());
            stmt.setInt(2, s.getId_curso());
            stmt.setInt(3, s.getId_turma());
            stmt.execute();
        }
    }

   public List<Serie> Buscar(String nome) throws SQLException {
        String sql = "select serie.id_serie,serie.nome_serie,serie.id_curso_s,c.nome_curso,serie.id_turma_s,descricao.descricao_desc from serie,descricao,curso c where serie.id_curso_s=c.id_curso and serie.id_turma_s=descricao.id_desc and descricao.id_curso_desc=c.id_curso and serie.nome_serie like ? order by serie.nome_serie asc,descricao.descricao_desc asc,c.nome_curso asc";
        List<Serie> minhaLista;
        try (PreparedStatement stmt = (PreparedStatement) conexao.prepareStatement(sql)) {
            stmt.setString(1, nome);
            try (ResultSet rs = stmt.executeQuery()) {
                minhaLista = new ArrayList<>();
                while (rs.next()) {
                    Serie a1 = new Serie();
                    a1.setId(rs.getInt("id_serie"));
                    a1.setNome(rs.getString("nome_serie"));
                    a1.setId_curso(rs.getInt("id_curso_s"));
                    a1.setCurso(rs.getString("nome_curso"));         
                    a1.setId_turma(rs.getInt("id_turma_s"));
                    a1.setTurma(rs.getString("descricao_desc"));
                    
                    minhaLista.add(a1);
                }
            }
        }
        return minhaLista;
    }

    public void Excluir(int id,Curso a) throws SQLException {
        if(VerificaSeHaHoraAula(id)){
         String sql_2 = "delete from hora_aula using descricao,curso,professor_serie_turma,serie where curso.id_curso=descricao.id_curso_desc and serie.id_curso_s=curso.id_curso and serie.id_turma_s=descricao.id_desc and professor_serie_turma.id_serie_turma_pst=serie.id_serie and hora_aula.id_serie_turma_ha=serie.id_serie and curso.nome_curso=? and descricao.descricao_desc=?";
            try (PreparedStatement consulta_2 = (PreparedStatement) conexao.prepareStatement(sql_2)) {
                consulta_2.setString(1, a.getNome());
                consulta_2.setString(2, a.getDescrição());
                consulta_2.execute();
            }
    }      
        if(VerificaSeHaHoraAula2(id)){
         String sql_3 = "delete from professor_serie_turma using serie where  professor_serie_turma.id_serie_turma_pst=serie.id_serie and serie.id_serie=?";
            try (PreparedStatement consulta_3 = (PreparedStatement) conexao.prepareStatement(sql_3)) {
                consulta_3.setInt(1, id);
                
                consulta_3.execute();
            }
        }     
         String sql_e = "delete from serie where id_serie=?";
        try (PreparedStatement consulta_e = (PreparedStatement) conexao.prepareStatement(sql_e)) {
            consulta_e.setLong(1, id);
            consulta_e.execute();
        }
    }

    public Serie BuscarSeriePorAnoeTurma(String ano, String turma) throws SQLException {
         String sql = "select serie.id_serie from serie,descricao where serie.id_turma_s=descricao.id_desc and serie.nome_serie like ? and descricao.descricao_desc like ?";
      Serie a1 = new Serie();
        try (PreparedStatement stmt = (PreparedStatement) conexao.prepareStatement(sql)) {
            stmt.setString(1, ano);
            stmt.setString(2, turma);
            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    
                    a1.setId(rs.getInt("id_serie"));
             
                }
            }
        }
        return a1;
        
            
       
        
    }

    public String[] BuscarAno() {
         String sql = "select DISTINCT nome_serie from serie order by nome_serie";
            String[] nome =null;
            
         
            Statement stmt;
        try {
            stmt = conexao.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        
       
            ResultSet rs= stmt.executeQuery(sql);   
                int i=0;
                    while (rs.next())
                    i++;                
                
                nome = new String[i];
                
                rs.beforeFirst();
                i=0;
                while (rs.next()){
                   nome[i] = rs.getString("nome_serie");
                    i++;
                    
                    
                
                }
            
           } catch (SQLException ex) {
            Logger.getLogger(SerieMet.class.getName()).log(Level.SEVERE, null, ex);
        }
        
          return nome; 
    }

    public int BuscarID(Serie s) {
        String sql = "select serie.id_serie from serie,descricao where serie.id_turma_s=descricao.id_desc and serie.nome_serie like ? and descricao.descricao_desc like ? ";
            int id=0;
         try {
            try (PreparedStatement stmt = (PreparedStatement) conexao.prepareStatement(sql)) {
                stmt.setString(1, s.getNome());
                stmt.setString(2, s.getTurma());
                try (ResultSet rs = stmt.executeQuery()) {
                    while (rs.next()){
                       
                    id=rs.getInt("id_serie");  
                        
                        
                    }
                }
            }
           
        } catch (SQLException ex) {
            Logger.getLogger(SerieMet.class.getName()).log(Level.SEVERE, null, ex);
        }
          return id;
    }

    public int BuscarQuantdeSerie() {
         String sql = "select * from serie";
        
        int num=0;
  
        
            
            PreparedStatement stmt;
        try {
            stmt = (PreparedStatement) conexao.prepareStatement(sql);
            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()){
                num++;
                }
            }
            stmt.close();
            System.out.println("sql1: " + sql);   
        } catch (SQLException ex) {
            Logger.getLogger(SerieMet.class.getName()).log(Level.SEVERE, null, ex);
        }
           
       
          return num;
    }

    public List<Serie> getList() throws SQLException {
        String sql = "select * from serie,descricao where serie.id_turma_s=descricao.id_desc order by serie.nome_serie asc, descricao.descricao_desc asc";

        PreparedStatement consulta_v;
        
            consulta_v = (PreparedStatement) conexao.prepareStatement(sql);
        List<Serie> minhaLista;
        try (ResultSet rs = consulta_v.executeQuery()) {
            minhaLista = new ArrayList<>();
            while (rs.next()) {
                Serie a1 = new Serie();
                a1.setNome(rs.getString("nome_serie"));
                a1.setTurma(rs.getString("descricao_desc"));
                a1.setId_curso(rs.getInt("id_curso_s"));
                minhaLista.add(a1);
                
            }
        }
        consulta_v.close();
        return minhaLista;
       

       
    }

    public List<Serie> getList3() throws SQLException {
         String sql = "select * from serie,descricao where serie.id_turma_s=descricao.id_desc and nome_serie='3' order by nome_serie asc, descricao_desc asc";
        List<Serie> minhaLista;
        try (PreparedStatement consulta_v = (PreparedStatement) conexao.prepareStatement(sql); ResultSet rs = consulta_v.executeQuery()) {
            minhaLista = new ArrayList<>();
            while (rs.next()) {
                Serie a1 = new Serie();
                a1.setNome(rs.getString("nome_serie"));
                a1.setTurma(rs.getString("descricao_desc"));
                a1.setId_curso(rs.getInt("id_curso_s"));
                minhaLista.add(a1);

            }
        }
        return minhaLista;
    }

   public List<Serie> BuscarListdeSeriesProf(int a) throws SQLException {
        String sql = "select serie.id_serie,serie.nome_serie,descricao.descricao_desc,serie.id_curso_s from serie,professor_serie_turma,descricao where descricao.id_desc=serie.id_turma?_s and serie.id_serie=professor_serie_turma.id_serie_turma_pst and professor_serie_turma.id_prof_pst like ?";
        List<Serie> minhaLista;
        try (PreparedStatement stmt = (PreparedStatement) conexao.prepareStatement(sql)) {
            stmt.setInt(1, a);
            try (ResultSet rs = stmt.executeQuery()) {
                minhaLista = new ArrayList<>();
                while (rs.next()) {
                    Serie a1 = new Serie();
                    a1.setId(rs.getInt("id_serie"));
                    a1.setNome(rs.getString("nome_serie"));
                    a1.setTurma(rs.getString("descricao_desc"));
                    a1.setId_curso(rs.getInt("id_curso_s"));
                    minhaLista.add(a1);

                }
            }
        }
        return minhaLista;
    }

   public int BuscaridSerie(String nome){
        String sql = "select * from serie where nome_serie like ?";
        int id=0;
  
        try {
            try (PreparedStatement stmt = (PreparedStatement) conexao.prepareStatement(sql)) {
                stmt.setString(1, nome);
                try (ResultSet rs = stmt.executeQuery()) {
                    while (rs.next()){
                        Serie s = new Serie();
                        
                        s.setId(rs.getInt("id_serie"));
                        id=s.getId();
                        
                    }
                }
            }
           
        } catch (SQLException ex) {
            Logger.getLogger(CursoMet.class.getName()).log(Level.SEVERE, null, ex);
        }
          return id;
    }
   
     public int BuscarIDTurma(String nome, String turma){
        String sql = "select * from serie,descricao where serie.id_turma_s=descricao.id_desc and nome_serie like ? and descricao.descricao_desc like ?";
        
        int id=0;
  
        try {
            try (PreparedStatement stmt = (PreparedStatement) conexao.prepareStatement(sql)) {
                stmt.setString(1, nome);
                stmt.setString(2, turma);
                try (ResultSet rs = stmt.executeQuery()) {
                    while (rs.next()){
                        Serie s = new Serie();
                        
                        s.setId(rs.getInt("id_serie"));
                        id=s.getId();
                        
                    }
                }
            }
            System.out.println("sql1: " + sql);   
        } catch (SQLException ex) {
            Logger.getLogger(CursoMet.class.getName()).log(Level.SEVERE, null, ex);
        }
          return id;
        }

    private boolean VerificaSeHaHoraAula(int id) {
        String sql = "select hora_aula.id_ha from hora_aula where  hora_aula.id_serie_turma_ha=?";
        int decisao=0;
        try {
            try (PreparedStatement stmt = (PreparedStatement) conexao.prepareStatement(sql)) {
                stmt.setInt(1, id);
                try (ResultSet rs = stmt.executeQuery()) {
                    while (rs.next()) {
                        if(rs.getInt("id_serie_turma_ha")==id){
                         decisao=1;   
                        }            
                        
                    }
                }
            }
        } catch (SQLException ex) {
            Logger.getLogger(DisciplinaMet.class.getName()).log(Level.SEVERE, null, ex);
        }
        
            if(decisao==1){
                return true;
            }else{
               return false; 
            }
    }

    private boolean VerificaSeHaHoraAula2(int id) {
        String sql = "select professor_serie_turma.id_serie_turma_pst from professor_serie_turma where  professor_serie_turma.id_serie_turma_pst=?";
        int decisao=0;
        try {
            try (PreparedStatement stmt = (PreparedStatement) conexao.prepareStatement(sql)) {
                stmt.setInt(1, id);
                try (ResultSet rs = stmt.executeQuery()) {
                    while (rs.next()) {
                        if(rs.getInt("id_serie_turma_pst")==id){
                         decisao=1;   
                        }            
                        
                    }
                }
            }
        } catch (SQLException ex) {
            Logger.getLogger(DisciplinaMet.class.getName()).log(Level.SEVERE, null, ex);
        }
        
            if(decisao==1){
                
                return true;
            }else{
               return false; 
            }
    }

    public String BuscaNomeSerie(int id_serie) {
        String sql = "select serie.nome_serie from serie where serie.id_serie=?";
        
        String id="";
  
        try {
            try (PreparedStatement stmt = (PreparedStatement) conexao.prepareStatement(sql)) {
                stmt.setInt(1, id_serie);
                
                try (ResultSet rs = stmt.executeQuery()) {
                    while (rs.next()){
                        
                     id=rs.getString("nome_serie");   
                        
                        
                    }
                }
            }
            
        } catch (SQLException ex) {
            Logger.getLogger(CursoMet.class.getName()).log(Level.SEVERE, null, ex);
        }
          return id;
    }

    public String BuscaTurma(int id_serie) {
         String sql = "select descricao.descricao_desc from serie, descricao where serie.id_turma_s=descricao.id_desc and serie.id_serie=?";
        
        String id="";
  
        try {
            try (PreparedStatement stmt = (PreparedStatement) conexao.prepareStatement(sql)) {
                stmt.setInt(1, id_serie);
                
                try (ResultSet rs = stmt.executeQuery()) {
                    while (rs.next()){
                        
                     id=rs.getString("descricao_desc");   
                        
                        
                    }
                }
            }
            
        } catch (SQLException ex) {
            Logger.getLogger(CursoMet.class.getName()).log(Level.SEVERE, null, ex);
        }
          return id;
    }

    public List<Serie> BuscaDadosRelTurmas() throws SQLException {
        String sql = "select serie.id_serie, serie.nome_serie, descricao.descricao_desc, curso.nome_curso from serie,curso,descricao where serie.id_curso_s=curso.id_curso and serie.id_turma_s=descricao.id_desc order by serie.nome_serie, descricao.descricao_desc";
        List<Serie> minhaLista;
        try (PreparedStatement stmt = (PreparedStatement) conexao.prepareStatement(sql)) {
            
            try (ResultSet rs = stmt.executeQuery()) {
                minhaLista = new ArrayList<>();
                while (rs.next()) {
                    Serie a1 = new Serie();
                    a1.setId(rs.getInt("id_serie"));
                    a1.setNome(rs.getString("nome_serie"));
                    a1.setTurma(rs.getString("descricao_desc"));
                    a1.setCurso(rs.getString("nome_curso"));
                    minhaLista.add(a1);

                }
            }
        }
        return minhaLista;
    }

}
